package com.happy3w.ssp.schoolmatehome.service.note;

import com.happy3w.ssp.schoolmatehome.entity.Note;
import com.happy3w.ssp.schoolmatehome.entity.User;
import com.happy3w.ssp.schoolmatehome.service.user.UserInfo;
import com.happy3w.ssp.schoolmatehome.service.user.UserStateSummary;
import com.happy3w.toolkits.utils.NameSqlBuilder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.function.LongFunction;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@RequiredArgsConstructor
@Component
public class NoteSearcher {
    private final NamedParameterJdbcTemplate jdbcTemplate;

    private static final String SELECT_SQL = """
            select n.id as noteId,u.id as userId,u.name as username,u.avatar as userAvatar,
            n.title as noteTitle, n.summary  as noteSummary, n.created_at as noteCreatedAt,
            ns.view_count,ns.favorite_count ,ns.collect_count\s""";
    private static final String FROM_NOTE_SQL = "from note n ";
    private static final String JOIN_STATE_SQL = "left join note_state ns on n.id=ns.note_id ";
    private static final String JOIN_USER_SQL = "left join user u on n.owner_id = u.id ";

    /*
    拼接类似sql
    from (select ntg.note_id,
            	sum(case when ntg.tag in (:mustTag) then 1 else 0 end) as mustMatch,
            	sum(case when ntg.tag in (:notTag) then 1 else 0 end) as notMatch
            	from note_tag ntg
            	group by ntg.note_id
            	having mustMatch=1 and notMatch=0
            	) as nts
            	left join note n on nts.note_id=n.id
     */
    private static final String FROM_TAG_NOTE_SQL = "from (select ntg.note_id";
    private static final String TAG_NOTE_MUST_FIELD = ",sum(case when ntg.tag in (:mustTag) then 1 else 0 end) as mustMatch";
    private static final String TAG_NOTE_NOT_FIELD = ",sum(case when ntg.tag in (:notTag) then 1 else 0 end) as notMatch";
    private static final String FROM_TAG_NOTE_GROUP_SQL = "from note_tag ntg group by ntg.note_id having";
    private static final String TAG_NOTE_MUST_CONDITION = "mustMatch=:mustTagCount";
    private static final String TAG_NOTE_NOT_CONDITION = "notMatch=0";
    private static final String FROM_TAG_NOTE_END = ") as nts";
    private static final String TAG_JOIN_NOTE_SQL = "left join note n on nts.note_id=n.id";


    private List<NoteSummary> findAllNotes(User curUser, LongFunction<NameSqlBuilder> buildSql) {
        long curUserId = curUser == null ? 0L : curUser.getId();
        NameSqlBuilder sqlBuilder = buildSql.apply(curUserId);
        return querySummary(sqlBuilder);
    }

    public List<NoteSummary> findAllSelf(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    FROM_NOTE_SQL, TAG_JOIN_NOTE_SQL)
                    .append("where n.owner_id=:ownerId ", userId);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by n.created_at desc ")
                    .appendPage(page, size);
        });
    }

    private void appendSearchByQuery(NameSqlBuilder builder, QueryItem queryItem) {
        if (queryItem.empty()) {
            return;
        }
        builder.append("and MATCH(title, summary, content) AGAINST(:fullQuery IN BOOLEAN MODE)", buildFullQuery(queryItem));
    }

    public List<NoteSummary> findRecommendNotes(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    FROM_NOTE_SQL, TAG_JOIN_NOTE_SQL)
                    .append("where n.scope=:scope", Note.SCOPE_PUBLIC);
            appendSearchByQuery(builder, query.getContents());

            builder.append("order by ");
            List<Long> followedUsers = jdbcTemplate.queryForList(
                    "select user_id from follow_state where fans_id=:fansId order by created_at desc limit 20",
                    Map.of("fansId", userId),
                    Long.class);
            if (!CollectionUtils.isEmpty(followedUsers)) {
                Date yesterday = new Date(System.currentTimeMillis() - 24 * 3600 * 1000);
                builder.append("case when n.created_at >:yesterday and  n.owner_id  in (:followedUsers) then 0 else 1 end,", yesterday, followedUsers);
            }
            return builder.append("n.created_at desc ")
                    .appendPage(page, size);
        });
    }

    private NameSqlBuilder buildQueryBuilderByTag(QueryItem tags,
                                                  String directSql,
                                                  String joinSql) {
        NameSqlBuilder builder = new NameSqlBuilder()
                .append(SELECT_SQL);
        if (tags.empty()) {
            builder.append(directSql);
        } else {
            buildTagQuery(builder, tags);
            builder.append(joinSql);
        }
        return builder.append(JOIN_STATE_SQL)
                .append(JOIN_USER_SQL);
    }

    private void buildTagQuery(NameSqlBuilder builder, QueryItem tags) {
        builder.append(FROM_TAG_NOTE_SQL);
        boolean doMust = !tags.getMust().isEmpty();
        boolean doNot = !tags.getNot().isEmpty();
        if (doMust) {
            builder.append(TAG_NOTE_MUST_FIELD, tags.getMust());
        }
        if (doNot) {
            builder.append(TAG_NOTE_NOT_FIELD, tags.getNot());
        }
        builder.append(FROM_TAG_NOTE_GROUP_SQL);
        if (doMust) {
            builder.append(TAG_NOTE_MUST_CONDITION, tags.getMust().size());
        }
        if (doNot) {
            if (doMust) {
                builder.append(" and ");
            }
            builder.append(TAG_NOTE_NOT_CONDITION);
        }
        builder.append(FROM_TAG_NOTE_END);
    }

    private String buildFullQuery(QueryItem queryItem) {
        return Stream.concat(
                        queryItem.getMust().stream()
                                .filter(StringUtils::hasText)
                                .distinct()
                                .map(item -> "+" + item + "*"),
                        queryItem.getNot().stream()
                                .filter(StringUtils::hasText)
                                .distinct()
                                .map(item -> "-" + item + "*"))
                .collect(Collectors.joining(" "));
    }

    private List<NoteSummary> querySummary(NameSqlBuilder sqlBuilder) {
        List<NoteSummaryDto> results = jdbcTemplate.query(sqlBuilder.getSql(),
                sqlBuilder.getParams(),
                BeanPropertyRowMapper.newInstance(NoteSummaryDto.class));
        return results.stream()
                .map(NoteSummaryDto::toSummary)
                .toList();
    }

    public List<NoteSummary> findAllNoteHistory(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    "from note_view nv left join note n on nv.note_id=n.id",
                    "left join note_view nv on nv.note_id=nts.note_id left join note n on nv.note_id=n.id")
                    .append("where nv.user_id=:userId and n.id is not null", userId);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by nv.updated_at desc ")
                    .appendPage(page, size);
        });
    }

    public List<NoteSummary> findAllNoteFavorite(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    "from note_favorite nf left join note n on nf.note_id=n.id",
                    "left join note_favorite nf on nf.note_id=nts.note_id left join note n on nf.note_id=n.id")
                    .append("where nf.user_id=:userId and n.id is not null", userId);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by nf.created_at desc ")
                    .appendPage(page, size);
        });
    }

    public List<NoteSummary> findAllNoteCollect(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    "from note_collect nc left join note n on nc.note_id=n.id",
                    "left join note_collect nc on nc.note_id=nts.note_id left join note n on nc.note_id=n.id")
                    .append("where nc.user_id=:userId and n.id is not null", userId);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by nc.created_at desc ")
                    .appendPage(page, size);
        });
    }

    public List<NoteSummary> findAllNoteTemplate(User curUser, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(curUser, userId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    "from note_template nt left join note n on nt.note_id=n.id",
                    "left join note_template nt on nt.note_id=nts.note_id left join note n on nt.note_id=n.id")
                    .append("where nt.user_id=:user_id and n.id is not null", userId)
                    .append("and (n.scope=:scope or n.owner_id=:owner_id)", Note.SCOPE_PUBLIC, userId);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by nt.created_at desc ")
                    .appendPage(page, size);
        });
    }


    public List<NoteSummary> findAllNotes(Long userId, String queryText, int page, int size) {
        NoteQuery query = NoteQuery.parse(queryText);
        return findAllNotes(null, curId -> {
            NameSqlBuilder builder = buildQueryBuilderByTag(query.getTags(),
                    FROM_NOTE_SQL,
                    TAG_JOIN_NOTE_SQL)
                    .append("where n.owner_id=:ownerId ", userId)
                    .append("and n.scope=:scope ", Note.SCOPE_PUBLIC);

            appendSearchByQuery(builder, query.getContents());
            return builder.append("order by n.created_at desc ")
                    .appendPage(page, size);
        });
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    private static class NoteSummaryDto {
        private Long noteId;
        private Long userId;
        private String userName;
        private String userAvatar;
        private String noteTitle;
        private String noteSummary;
        private Date noteCreatedAt;
        private Integer viewCount;
        private Integer favoriteCount;
        private Integer collectCount;

        public NoteSummary toSummary() {
            return NoteSummary.builder()
                    .id(noteId)
                    .owner(UserInfo.builder()
                            .id(userId)
                            .name(userName)
                            .avatar(userAvatar)
                            .build())
                    .title(noteTitle)
                    .summary(noteSummary)
                    .state(UserStateSummary.builder()
                            .viewCount(viewCount == null ? 0 : viewCount)
                            .favoriteCount(favoriteCount == null ? 0 : favoriteCount)
                            .collectCount(collectCount == null ? 0 : collectCount)
                            .build())
                    .createdAt(noteCreatedAt)
                    .build();
        }
    }
}
